const mysql = require('mysql2/promise');

async function testTimestampFunctionality() {
    const connection = await mysql.createConnection({
        host: process.env.DB_HOST || '43.143.253.188',
        user: process.env.DB_USER || 'nextjs',
        password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
        database: process.env.DB_NAME || 'nextjs'
    });

    try {
        console.log('🧪 测试时间戳功能...');

        // 测试1: 检查表结构
        console.log('\n📋 测试1: 检查表结构');
        const [tables] = await connection.execute('SHOW TABLES');
        const testTable = 'users'; // 使用users表进行测试

        if (tables.some(table => Object.values(table)[0] === testTable)) {
            const [columns] = await connection.execute(`SHOW COLUMNS FROM ${testTable}`);
            const hasCreatedAt = columns.some(col => col.Field === 'created_at');
            const hasUpdatedAt = columns.some(col => col.Field === 'updated_at');

            console.log(`✅ ${testTable}表结构:`);
            console.log(`  - created_at字段: ${hasCreatedAt ? '存在' : '不存在'}`);
            console.log(`  - updated_at字段: ${hasUpdatedAt ? '存在' : '不存在'}`);

            if (hasCreatedAt) {
                const createdAtCol = columns.find(col => col.Field === 'created_at');
                console.log(`  - created_at类型: ${createdAtCol.Type}`);
                console.log(`  - created_at默认值: ${createdAtCol.Default || '无'}`);
            }

            if (hasUpdatedAt) {
                const updatedAtCol = columns.find(col => col.Field === 'updated_at');
                console.log(`  - updated_at类型: ${updatedAtCol.Type}`);
                console.log(`  - updated_at默认值: ${updatedAtCol.Default || '无'}`);
            }
        }

        // 测试2: 检查触发器
        console.log('\n📋 测试2: 检查触发器');
        const [triggers] = await connection.execute('SHOW TRIGGERS');
        const timestampTriggers = triggers.filter(trigger =>
            trigger.Trigger.includes('created_at') || trigger.Trigger.includes('updated_at')
        );

        console.log(`✅ 找到 ${timestampTriggers.length} 个时间戳触发器:`);
        timestampTriggers.forEach(trigger => {
            console.log(`  - ${trigger.Trigger} (${trigger.Event} on ${trigger.Table})`);
        });

        // 测试3: 测试插入数据
        console.log('\n📋 测试3: 测试插入数据');
        const testMobile = `test_${Date.now()}`;
        const testPassword = 'test_password_hash';

        try {
            await connection.execute(`
        INSERT INTO users (mobile, passwordHash) 
        VALUES (?, ?)
      `, [testMobile, testPassword]);

            console.log('✅ 成功插入测试数据');

            // 检查插入后的时间戳
            const [result] = await connection.execute(`
        SELECT created_at, updated_at 
        FROM users 
        WHERE mobile = ?
      `, [testMobile]);

            if (result.length > 0) {
                const user = result[0];
                console.log(`✅ 插入后的时间戳:`);
                console.log(`  - created_at: ${user.created_at}`);
                console.log(`  - updated_at: ${user.updated_at}`);

                // 测试更新数据
                console.log('\n📋 测试4: 测试更新数据');
                await connection.execute(`
          UPDATE users 
          SET passwordHash = ? 
          WHERE mobile = ?
        `, ['updated_password_hash', testMobile]);

                // 检查更新后的时间戳
                const [updatedResult] = await connection.execute(`
          SELECT created_at, updated_at 
          FROM users 
          WHERE mobile = ?
        `, [testMobile]);

                if (updatedResult.length > 0) {
                    const updatedUser = updatedResult[0];
                    console.log(`✅ 更新后的时间戳:`);
                    console.log(`  - created_at: ${updatedUser.created_at}`);
                    console.log(`  - updated_at: ${updatedUser.updated_at}`);

                    // 验证时间戳是否正确
                    if (updatedUser.created_at && updatedUser.updated_at) {
                        const createdTime = new Date(updatedUser.created_at);
                        const updatedTime = new Date(updatedUser.updated_at);

                        if (updatedTime > createdTime) {
                            console.log('✅ 时间戳验证成功: 更新时间晚于创建时间');
                        } else {
                            console.log('❌ 时间戳验证失败: 更新时间不晚于创建时间');
                        }
                    }
                }
            }

            // 清理测试数据
            await connection.execute(`DELETE FROM users WHERE mobile = ?`, [testMobile]);
            console.log('✅ 清理测试数据完成');

        } catch (error) {
            console.log(`❌ 测试插入数据失败: ${error.message}`);
        }

        console.log('\n🎉 时间戳功能测试完成！');

    } catch (error) {
        console.error('❌ 测试过程中出错:', error);
    } finally {
        await connection.end();
    }
}

// 运行测试
testTimestampFunctionality().catch(console.error);
